
clickhouse-client --password Keepgo123@cys -q "drop view IF EXISTS  db_cfx.mv_asi_data_user_cpa_new"
clickhouse-client --password Keepgo123@cys -q "drop view IF EXISTS  db_cfx.mv_asi_data_user_cpa_new_deduction"
clickhouse-client --password Keepgo123@cys -q "drop view IF EXISTS  db_cfx.mv_asid_data_user_cpa_active"
clickhouse-client --password Keepgo123@cys -q "drop view IF EXISTS  db_cfx.mv_otherappid_data_user_cpa_active"
clickhouse-client --password Keepgo123@cys -q "drop view IF EXISTS  db_cfx.mv_otherappid_data_user_cpa_new"
clickhouse-client --password Keepgo123@cys -q "drop view IF EXISTS  db_cfx.mv_otherappid_data_user_cpa_new_deduction"

clickhouse-client --password Keepgo123@cys -q "
CREATE MATERIALIZED VIEW db_cfx.mv_asi_data_user_cpa_new TO db_cfx.t_data_user_cpa_new
(
    channel String,
    dev_brand String,
    dev_model String,
    os_version String,
    oaid String,
    vn String,
    vc String,
    gaid String,
    maid String,
    activate_time DateTime64(3, 'Asia/Shanghai'),
    activate_ip String,
    status String
)
AS WITH incoming AS
    (
        SELECT *
        FROM
        (
            SELECT
                argMin(channel, base_create_time) AS channel,
                argMin(brand, base_create_time) AS dev_brand,
                argMin(model, base_create_time) AS dev_model,
                argMin(osv, base_create_time) AS os_version,
                uuid,
                argMin(vn, base_create_time) AS vn,
                argMin(vc, base_create_time) AS vc,
                argMin(gaid, base_create_time) AS gaid,
                argMin(maid, base_create_time) AS maid,
                min(base_create_time) AS activate_time,
                argMin(base_client_ip, base_create_time) AS activate_ip,
                argMin(base_status, base_create_time) AS status,
                argMin(lockscreen_setting_state, base_create_time) AS lockscreen_setting_state
            FROM db_cfx.t_log_base_data_standard_active
            WHERE (base_create_date = today()) AND (base_app_id = 'AnySelectedID') AND (base_create_time >= (now() - toIntervalHour(1)))
            GROUP BY uuid
        )
        WHERE (vn > '3.37') AND (lockscreen_setting_state != 'false')
    )
SELECT
    i.channel AS channel,
    i.dev_brand AS dev_brand,
    i.dev_model AS dev_model,
    i.os_version AS os_version,
    i.uuid AS oaid,
    i.vn AS vn,
    i.vc AS vc,
    i.gaid AS gaid,
    i.maid AS maid,
    i.activate_time AS activate_time,
    i.activate_ip AS activate_ip,
    i.status AS status
FROM incoming AS i
ANTI LEFT JOIN db_cfx.t_data_user_cpa_new AS f ON i.uuid = f.oaid
ANTI LEFT JOIN (select oaid from db_cfx.t_data_user_cpa_new_deduction where channel_name='lockscreen_false' ) AS d ON i.uuid = d.oaid"

clickhouse-client --password Keepgo123@cys -q "
CREATE MATERIALIZED VIEW db_cfx.mv_asi_data_user_cpa_new_deduction TO db_cfx.t_data_user_cpa_new_deduction
(
    channel_name String,
    channel String,
    dev_brand String,
    dev_model String,
    os_version String,
    oaid String,
    vn String,
    vc String,
    gaid String,
    maid String,
    activate_time DateTime64(3, 'Asia/Shanghai'),
    activate_ip String,
    status String
)
AS WITH incoming AS
    (
        SELECT *
        FROM
        (
            SELECT
                argMin(channel, base_create_time) AS channel,
                argMin(brand, base_create_time) AS dev_brand,
                argMin(model, base_create_time) AS dev_model,
                argMin(osv, base_create_time) AS os_version,
                uuid,
                argMin(vn, base_create_time) AS vn,
                argMin(vc, base_create_time) AS vc,
                argMin(gaid, base_create_time) AS gaid,
                argMin(maid, base_create_time) AS maid,
                min(base_create_time) AS activate_time,
                argMin(base_client_ip, base_create_time) AS activate_ip,
                argMin(base_status, base_create_time) AS status,
                argMin(lockscreen_setting_state, base_create_time) AS lockscreen_setting_state
            FROM db_cfx.t_log_base_data_standard_active
            WHERE (base_create_date = today()) AND (base_app_id = 'AnySelectedID') AND (base_create_time >= (now() - toIntervalHour(1)))
            GROUP BY uuid
        )
        WHERE (vn > '3.37') AND (lockscreen_setting_state = 'false')
    )
SELECT
    'lockscreen_false' AS channel_name,
    i.channel AS channel,
    i.dev_brand AS dev_brand,
    i.dev_model AS dev_model,
    i.os_version AS os_version,
    i.uuid AS oaid,
    i.vn AS vn,
    i.vc AS vc,
    i.gaid AS gaid,
    i.maid AS maid,
    i.activate_time AS activate_time,
    i.activate_ip AS activate_ip,
    i.status AS status
FROM incoming AS i
ANTI LEFT JOIN db_cfx.t_data_user_cpa_new AS f ON i.uuid = f.oaid
ANTI LEFT JOIN db_cfx.t_data_user_cpa_new_deduction AS d ON i.uuid = d.oaid
"

clickhouse-client --password Keepgo123@cys -q "
CREATE MATERIALIZED VIEW db_cfx.mv_asid_data_user_cpa_active TO db_cfx.t_data_user_cpa_active
(
    channel String,
    dev_brand String,
    dev_model String,
    os_version String,
    oaid String,
    vn String,
    vc String,
    gaid String,
    maid String,
    activate_time DateTime64(3, 'Asia/Shanghai'),
    activate_ip String,
    status String
)
AS SELECT
    i.channel AS channel,
    i.dev_brand AS dev_brand,
    i.dev_model AS dev_model,
    i.os_version AS os_version,
    i.oaid AS oaid,
    i.vn AS vn,
    i.vc AS vc,
    i.gaid AS gaid,
    i.maid AS maid,
    i.activate_time AS activate_time,
    i.activate_ip AS activate_ip,
    i.status AS status
FROM
(
    SELECT
        argMax(channel, base_create_time) AS channel,
        argMax(brand, base_create_time) AS dev_brand,
        argMax(model, base_create_time) AS dev_model,
        argMax(osv, base_create_time) AS os_version,
        uuid AS oaid,
        argMax(vn, base_create_time) AS vn,
        argMax(vc, base_create_time) AS vc,
        argMax(gaid, base_create_time) AS gaid,
        argMax(maid, base_create_time) AS maid,
        max(base_create_time) AS activate_time,
        argMax(base_client_ip, base_create_time) AS activate_ip,
        argMax(base_status, base_create_time) AS status
    FROM db_cfx.t_log_base_data_standard_active
    WHERE (base_create_date = today()) AND (base_app_id = 'AnySelectedID') AND (base_create_time >= (now() - toIntervalHour(1))) AND (lockscreen_setting_state != 'false')
    GROUP BY oaid
) AS i
ANTI LEFT JOIN
(
    SELECT DISTINCT oaid
    FROM db_cfx.t_data_user_cpa_active
    WHERE activate_date = today()
) AS f ON i.oaid = f.oaid
ANTI LEFT JOIN (select oaid from db_cfx.t_data_user_cpa_new_deduction where channel_name='lockscreen_false' ) AS d ON i.oaid = d.oaid
WHERE i.vn > '3.37'
"

clickhouse-client --password Keepgo123@cys -q "
CREATE MATERIALIZED VIEW db_cfx.mv_otherappid_data_user_cpa_active TO db_cfx.t_data_user_cpa_active
(
    channel String,
    dev_brand String,
    dev_model String,
    os_version String,
    oaid String,
    vn String,
    vc String,
    gaid String,
    maid String,
    activate_time DateTime64(3, 'Asia/Shanghai'),
    activate_ip String,
    status String
)
AS SELECT
    i.channel AS channel,
    i.dev_brand AS dev_brand,
    i.dev_model AS dev_model,
    i.os_version AS os_version,
    i.oaid AS oaid,
    i.vn AS vn,
    i.vc AS vc,
    i.gaid AS gaid,
    i.maid AS maid,
    i.activate_time AS activate_time,
    i.activate_ip AS activate_ip,
    i.status AS status
FROM
(
    SELECT
        argMax(channel, base_create_time) AS channel,
        argMax(brand, base_create_time) AS dev_brand,
        argMax(model, base_create_time) AS dev_model,
        argMax(osv, base_create_time) AS os_version,
        uuid AS oaid,
        argMax(vn, base_create_time) AS vn,
        argMax(vc, base_create_time) AS vc,
        argMax(gaid, base_create_time) AS gaid,
        argMax(maid, base_create_time) AS maid,
        max(base_create_time) AS activate_time,
        argMax(base_client_ip, base_create_time) AS activate_ip,
        argMax(base_status, base_create_time) AS status
    FROM db_cfx.t_log_base_data_standard_active
    WHERE (base_create_date = today()) AND (base_app_id != 'AnySelectedID') AND (base_create_time >= (now() - toIntervalHour(1))) AND (lockscreen_setting_state != 'false')
    GROUP BY oaid
) AS i
ANTI LEFT JOIN
(
    SELECT DISTINCT oaid
    FROM db_cfx.t_data_user_cpa_active
    WHERE activate_date = today()
) AS f ON i.oaid = f.oaid
ANTI LEFT JOIN (select oaid from db_cfx.t_data_user_cpa_new_deduction where channel_name='lockscreen_false' ) AS d ON i.oaid = d.oaid
"

clickhouse-client --password Keepgo123@cys -q "
CREATE MATERIALIZED VIEW db_cfx.mv_otherappid_data_user_cpa_new TO db_cfx.t_data_user_cpa_new
(
    channel String,
    dev_brand String,
    dev_model String,
    os_version String,
    oaid String,
    vn String,
    vc String,
    gaid String,
    maid String,
    activate_time DateTime64(3, 'Asia/Shanghai'),
    activate_ip String,
    status String
)
AS WITH incoming AS
    (
        SELECT
            argMin(channel, base_create_time) AS channel,
            argMin(brand, base_create_time) AS dev_brand,
            argMin(model, base_create_time) AS dev_model,
            argMin(osv, base_create_time) AS os_version,
            uuid,
            argMin(vn, base_create_time) AS vn,
            argMin(vc, base_create_time) AS vc,
            argMin(gaid, base_create_time) AS gaid,
            argMin(maid, base_create_time) AS maid,
            min(base_create_time) AS activate_time,
            argMin(base_client_ip, base_create_time) AS activate_ip,
            argMin(base_status, base_create_time) AS status
        FROM db_cfx.t_log_base_data_standard_active
        WHERE (base_create_date = today()) AND (base_app_id != 'AnySelectedID') AND (lockscreen_setting_state != 'false') AND (base_create_time >= (now() - toIntervalHour(1)))
        GROUP BY uuid
    )
SELECT
    i.channel AS channel,
    i.dev_brand AS dev_brand,
    i.dev_model AS dev_model,
    i.os_version AS os_version,
    i.uuid AS oaid,
    i.vn AS vn,
    i.vc AS vc,
    i.gaid AS gaid,
    i.maid AS maid,
    i.activate_time AS activate_time,
    i.activate_ip AS activate_ip,
    i.status AS status
FROM incoming AS i
ANTI LEFT JOIN db_cfx.t_data_user_cpa_new AS f ON i.uuid = f.oaid
ANTI LEFT JOIN (select oaid from db_cfx.t_data_user_cpa_new_deduction where channel_name='lockscreen_false' ) AS d ON i.uuid = d.oaid
"

clickhouse-client --password Keepgo123@cys -q "
CREATE MATERIALIZED VIEW db_cfx.mv_otherappid_data_user_cpa_new_deduction TO db_cfx.t_data_user_cpa_new_deduction
(
    channel_name String,
    channel String,
    dev_brand String,
    dev_model String,
    os_version String,
    oaid String,
    vn String,
    vc String,
    gaid String,
    maid String,
    activate_time DateTime64(3, 'Asia/Shanghai'),
    activate_ip String,
    status String
)
AS WITH incoming AS
    (
        SELECT *
        FROM
        (
            SELECT
                argMin(channel, base_create_time) AS channel,
                argMin(brand, base_create_time) AS dev_brand,
                argMin(model, base_create_time) AS dev_model,
                argMin(osv, base_create_time) AS os_version,
                uuid,
                argMin(vn, base_create_time) AS vn,
                argMin(vc, base_create_time) AS vc,
                argMin(gaid, base_create_time) AS gaid,
                argMin(maid, base_create_time) AS maid,
                min(base_create_time) AS activate_time,
                argMin(base_client_ip, base_create_time) AS activate_ip,
                argMin(base_status, base_create_time) AS status,
                argMin(lockscreen_setting_state, base_create_time) AS lockscreen_setting_state
            FROM db_cfx.t_log_base_data_standard_active
            WHERE (base_app_id != 'AnySelectedID') AND (base_create_time >= (now() - toIntervalHour(1))) AND (base_create_date = today())
            GROUP BY uuid
        )
        WHERE lockscreen_setting_state = 'false'
    )
SELECT
    'lockscreen_false' AS channel_name,
    i.channel AS channel,
    i.dev_brand AS dev_brand,
    i.dev_model AS dev_model,
    i.os_version AS os_version,
    i.uuid AS oaid,
    i.vn AS vn,
    i.vc AS vc,
    i.gaid AS gaid,
    i.maid AS maid,
    i.activate_time AS activate_time,
    i.activate_ip AS activate_ip,
    i.status AS status
FROM incoming AS i
ANTI LEFT JOIN db_cfx.t_data_user_cpa_new AS f ON i.uuid = f.oaid
ANTI LEFT JOIN db_cfx.t_data_user_cpa_new_deduction AS d ON i.uuid = d.oaid
"